Re: Can't figure out how to use now() in default for tsrange column (PG 9.2)

>Chris Bartlett <c.bartlett@paradise.net.nz> writes:
>>  I'm trying to set [now(), 2049-12-31 00:00:00) as the default for a
>>  tsrange column (Postgres 9.2), but can't figure out how to do it. I'm
>>  either getting syntax errors or now() is being evaluated, so that the
>>  default becomes something like [2012-07-14 14:04:35, 2049-12-31
>>  00:00:00), which is not what I want. Can anyone point me in the right
>>  direction, please?
>
>I think you'd need to use the constructor function, ie
>
>    default tsrange(now(), '2049-12-31 00:00:00')

I had tried the constructor function and hadn't managed to get a
successful result. E.g.

alter table the_table alter column the_column set default
tsrange(now(), '2049-12-31 00:00:00');
-> ERROR:  function tsrange(timestamp with time zone, unknown) does not exist
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

Realisation: now() is a timestamp with time zone, but my column is a
timestamp without time zone. So this works:

alter table the_table alter column the_column set default
tsrange(now()::timestamp without time zone, '2049-12-31
00:00:00'::timestamp without time zone);
-> ALTER TABLE

>BTW, that second value looks a whole lot like a poorly thought out
>substitute for 'infinity' ...
>            regards, tom lane


That's certainly an interesting comment and I'm open to suggestions!
The original db has two columns (from_timestamp, to_timestamp). I
don't go for NULL in the to_timestamp column. Alternatively, a
timestamp very, very far in the future can throw off query planners.

Thanks,
Chris